package br.unisinos.ubicomp.locationawaretaskmanager;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.google.android.maps.GeoPoint;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class TaskDBHandler extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
 
    private static final String DATABASE_NAME = "taskManager.db";
    private static final String TABLE_TASKS = "tasks";
    
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_LAT = "latitude";
    private static final String KEY_LON = "longitude";
    private static final String KEY_DATE = "date";
	
    public TaskDBHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
    
	public TaskDBHandler(Context context, String name, CursorFactory factory,
			int version) {
		super(context, name, factory, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		 String CREATE_TASKS_TABLE = "CREATE TABLE " + TABLE_TASKS + " ("
	                + KEY_ID + " INTEGER PRIMARY KEY, " + KEY_NAME + " TEXT, "
	                + KEY_LAT + " TEXT, " + KEY_LON + " TEXT, " + KEY_DATE + " TEXT" + " );";
		 
	     db.execSQL(CREATE_TASKS_TABLE);
		
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TASKS);
        onCreate(db);
	}

	// Adding a new Task
	public void addTask(Task task) {
		SQLiteDatabase db = this.getWritableDatabase();
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_ID, getNextId());
	    values.put(KEY_NAME, task.getName());
	    values.put(KEY_LAT,  String.valueOf(task.getLocation().getLatitudeE6()));
	    values.put(KEY_LON,  String.valueOf(task.getLocation().getLongitudeE6()));
	    values.put(KEY_DATE, task.getDate().getTime());
	 
	  	db.insert(TABLE_TASKS, null, values);
	    db.close();
	}
	 
	// Get single task
	public Task getTask(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		 
	    Cursor cursor = db.query(TABLE_TASKS, null, KEY_ID + "=?",
	            new String[] { String.valueOf(id) }, null, null, null, null);
	    if (cursor != null)
	        cursor.moveToFirst();
	 
	    Task task = new Task(cursor.getString(1),
	                         new GeoPoint(Integer.valueOf(cursor.getString(2)), 
	                         		      Integer.valueOf(cursor.getString(3))  ),
	                         new Date(Long.valueOf(cursor.getString(4))) );
	    task.setId(Integer.valueOf(cursor.getString(0)));
	    
	    cursor.close();
	    return task;	
	}
	 
	// Get All tasks
	public List<Task> getAllTasks() {
		List<Task> taskList = new ArrayList<Task>();
		String selectQuery = "SELECT  * FROM " + TABLE_TASKS;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				Task task = new Task();
				try{
				task.setId(Integer.parseInt(cursor.getString(0)));
				task.setName(cursor.getString(1));
				task.setLocation(new GeoPoint(Integer.valueOf(cursor.getString(2)), 
											  Integer.valueOf(cursor.getString(3))) );
				task.setDate(new Date(Long.valueOf(cursor.getString(4))));
				} catch(NumberFormatException exc) {
					task.setDate(new Date());
				}	
				taskList.add(task);
			} while (cursor.moveToNext());
		}
		
		cursor.close();

		return taskList;
	}
	 
	// Get tasks Count
	public int getTasksCount() {
		String countQuery = "SELECT  * FROM " + TABLE_TASKS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        
        int result = cursor.getCount();
        cursor.close();
 
        return result; 
	}
	
	// Update single task
	public int updateTask(Task task) {
		SQLiteDatabase db = this.getWritableDatabase();
		 
		ContentValues values = new ContentValues();
	    values.put(KEY_NAME, task.getName());
	    values.put(KEY_LAT, String.valueOf(task.getLocation().getLatitudeE6()));
	    values.put(KEY_LON, String.valueOf(task.getLocation().getLongitudeE6()));
	    values.put(KEY_DATE, task.getDate().getTime());
	 
	    int result = db.update(TABLE_TASKS, values, KEY_ID + " = ?",
	                           new String[] { String.valueOf(task.getId()) });
	    db.close();
	    
	    return result;
	}
	 
	// Delete single task
	public void deleteTask(Task task) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_TASKS, KEY_ID + " = ?",
				  new String[] { String.valueOf(task.getId()) });
		
		db.close();
	}
	
	public int getNextId() {
		return getTasksCount() + 1;
	}

}
